var express = require('express');
var router = express.Router();

var mysql = require('mysql');
var client = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123',
    database:'blog',
    port: 3306
});
client.connect();
router.get('/',function(req,res,next){

	client.query('select * from catalog', function(err, rows, fields) {
		if (err) throw err;
		var catalog = rows;
		client.query('select *,articles.id as aid from articles join catalog on cata_id=catalog.id order by articles.id desc limit 10 ', function(err, rows, fields) {
		if (err) throw err;
		res.render('index',{articles:rows,catalog:catalog,cata_id:0});
		});
	});
});

router.get('/news',function(req,res,next){

	client.query('select * from catalog', function(err, rows, fields) {
		if (err) throw err;
		var catalog = rows;
		client.query('select *,articles.id as aid from articles where cata_id='+req.query.id+' limit 10', function(err, rows, fields) {
		if (err) throw err;
		res.render('list',{articles:rows,catalog:catalog,cata_id:req.query.id});
		});
	});
});

router.get('/article',function(req,res,next){

	client.query('select * from catalog', function(err, rows, fields) {
		if (err) throw err;
		var catalog = rows;
		client.query('select * from articles where articles.id='+req.query.id, function(err, rows, fields) {
		if (err) throw err;
		res.render('article',{article:rows[0],catalog:catalog,cata_id:rows[0].cata_id});
		});
	});
});

router.get('/login',function(req,res,next){
		res.render('login',{});
});
	

/*router.post('/addmessage.html',function(req,res,next){
	
	client.query('insert into messagebord values(null,"'+req.body.author+'","'+req.body.message+'","'+new Date()+'")', function(err, rows, fields) {
		if (err) throw err;
		
	    res.redirect('/');
	});
});

router.get('/delmessage.html',function(req,res,next){

	client.query('delete from messagebord where id='+req.query.id, function(err, rows, fields) {
		if (err) throw err;
		
	    res.redirect('/');
	});
});*/

/* GET home page. */
/*router.get('/', function(req, res, next) {
  res.render('index', { title: '留言板' });
});*/

module.exports = router;
